﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace QLNHASACH.DataAccessLayer
{
    class DataAccess
    {
        protected static string strConn = @"Data Source=DUCLOI-PC\SQLEXPRESS;Initial Catalog=QLNHASACH;Integrated Security=True";
        private static SqlConnection connection;
        private static SqlDataAdapter dataAp;
        private static DataTable dataTable;
        //Mo ket noi
        public static void OpenConnection()
        {
            try
            {
                connection = new SqlConnection(strConn);
                connection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi: " + ex.Message);
            }
        }
        //Dong ket noi
        public static void CloseConnection()
        {
            if (connection != null)
                connection.Close();
        }
        //Ham lay du lieu datatable produce truyen vao
        public static DataTable GetDataTable(string sql)
        {
            OpenConnection();
            //Tao dataApdatpter, thu hien len query
            dataAp = new SqlDataAdapter(sql,connection);
            //Do du lieu vao DataTable
            dataTable = new DataTable();
            dataAp.Fill(dataTable);
            CloseConnection();
            return dataTable;          
        }
        //Ham thuc hie cau truy van inset, update, delete
        public static bool ExecuteQuery(string sql)
        {
            OpenConnection();
            int numRecordsEffect = 0;
            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                SqlCommand cmd = new SqlCommand(sql,connection);
                numRecordsEffect = cmd.ExecuteNonQuery();
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Loi :" + ex.Message);
            }
            CloseConnection();
            if (numRecordsEffect > 0)
                return true;
            return false;
        }

        public static DataTable SearchKH(string strSearch)
        {
            OpenConnection();
            DataTable table=new DataTable();
            try
            {
                string strProcduce="SearchTenKhachHang";
                SqlCommand com =new SqlCommand(strProcduce, connection);
                com.CommandType = CommandType.StoredProcedure;

                SqlParameter thamso=new SqlParameter();
                thamso.ParameterName = "@TenKhachHang";
                thamso.Value = strSearch;

                com.Parameters.Add(thamso);
                com.ExecuteNonQuery();

               
                SqlDataAdapter data=new SqlDataAdapter(com);
                data.Fill(table);
            }
            catch
            {
                return null;
            }
            return table;
        }
    }
}
